/*
 Navicat Premium Data Transfer

 Source Server         : demo
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : localhost:3306
 Source Schema         : month3-33

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 26/01/2024 14:04:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for city
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of city
-- ----------------------------
BEGIN;
INSERT INTO `city` (`id`, `name`) VALUES (1, '台湾');
INSERT INTO `city` (`id`, `name`) VALUES (2, '广东');
INSERT INTO `city` (`id`, `name`) VALUES (3, '北京');
INSERT INTO `city` (`id`, `name`) VALUES (4, '深圳');
INSERT INTO `city` (`id`, `name`) VALUES (5, '上海');
INSERT INTO `city` (`id`, `name`) VALUES (6, '重庆');
COMMIT;

-- ----------------------------
-- Table structure for house
-- ----------------------------
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '楼盘名称',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '楼盘地址',
  `province_id` int DEFAULT NULL COMMENT '省id',
  `city_id` int DEFAULT NULL COMMENT '市id',
  `pic_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片地址',
  `area` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建筑面积',
  `sms` int DEFAULT NULL COMMENT '是否已发送短信  0否 1是',
  PRIMARY KEY (`id`),
  KEY `idx_region` (`province_id`,`city_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of house
-- ----------------------------
BEGIN;
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (1, '碧桂园小区', '和平路1号', 1, 4, NULL, '1000', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (2, '保利小区', '健康路1号', 2, 9, NULL, '2000', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (3, '融创小区', '胜利路1号', 3, 13, NULL, '3000', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (4, '绿地小区', '建国路1号', 3, 14, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (5, '绿地小区3区', '建国路1号', 3, 15, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (6, '绿地小区2区', '建国路1号', 3, 15, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (7, '绿地小区4区', '建国路1号', 3, 16, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (8, '绿地小区5区', '建国路1号', 3, 16, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (9, '绿地小区6区', '建国路1号', 3, 17, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (10, '万达小区1号', '晋城高平市', 3, 17, 'http://localhost:8080/static/1672124638925saber02.jpeg', '200', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (11, 'dddd', 'ddddd', 1, 4, 'http://localhost:8080/static/1672195230428saber02.jpeg', '200', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (12, '但是风格的复古的', '豆腐干豆腐豆腐', 1, 4, 'http://localhost:8080/static/036fbb60-b11d-477a-baff-a6ceb0beca81kung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (13, 'SDFBGDFBDG', 'DGDGDGD', 1, 5, 'http://localhost:8080/static/c794a72f-9298-4dbb-a13e-0f7bf7e756e5kung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (14, 'cc hu', 'tianjin', 1, 4, 'http://localhost:8080/static/92040fa2-4c45-4751-b806-ec40b6e2559fkung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (15, 'cc hu', 'tianjin', 2, 9, 'http://localhost:8080/static/4e3b4358-70c9-4cae-9430-45016839c76dkung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (16, 'cc hu', 'tianjin', 3, 16, 'http://localhost:8080/static/6a8f2cc7-ce6c-4e45-95ba-4a0f9fcf8a61test01.jpg', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (17, '恒大001', '天津001', 3, 13, 'http://localhost:8080/static/img/kung.png', NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for region
-- ----------------------------
DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
  `parent_id` int DEFAULT NULL COMMENT '父级id',
  `level` int DEFAULT NULL COMMENT '0 代表省,  1代表市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of region
-- ----------------------------
BEGIN;
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (1, '河北省', -1, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (2, '山东省', -1, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (3, '山西省', -1, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (4, '廊坊市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (5, '邢台市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (6, '唐山市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (7, '石家庄市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (8, '邯郸市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (9, '济南市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (10, '潍坊市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (11, '青岛市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (12, '菏泽市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (13, '太原市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (14, '阳泉市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (15, '吕梁市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (16, '大同市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (17, '晋城市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (18, '万柏林区', 13, 2);
COMMIT;

-- ----------------------------
-- Table structure for yiqing
-- ----------------------------
DROP TABLE IF EXISTS `yiqing`;
CREATE TABLE `yiqing` (
  `id` int NOT NULL AUTO_INCREMENT,
  `city_id` int DEFAULT NULL,
  `now_ definite` int DEFAULT NULL,
  `definite` int DEFAULT NULL,
  `death` int DEFAULT NULL,
  `cure` int DEFAULT NULL,
  `yesterday` int DEFAULT NULL COMMENT '是否昨日新增  0 否 1是',
  PRIMARY KEY (`id`),
  KEY `idx_yset` (`yesterday`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of yiqing
-- ----------------------------
BEGIN;
INSERT INTO `yiqing` (`id`, `city_id`, `now_ definite`, `definite`, `death`, `cure`, `yesterday`) VALUES (1, 1, 500, 5000, 2000, 3000, 0);
INSERT INTO `yiqing` (`id`, `city_id`, `now_ definite`, `definite`, `death`, `cure`, `yesterday`) VALUES (2, 1, 500, 500, 200, 300, 1);
INSERT INTO `yiqing` (`id`, `city_id`, `now_ definite`, `definite`, `death`, `cure`, `yesterday`) VALUES (3, 2, 600, 3000, 4000, 2000, 0);
INSERT INTO `yiqing` (`id`, `city_id`, `now_ definite`, `definite`, `death`, `cure`, `yesterday`) VALUES (4, 2, 500, 350, 450, 260, 1);
INSERT INTO `yiqing` (`id`, `city_id`, `now_ definite`, `definite`, `death`, `cure`, `yesterday`) VALUES (5, 3, 700, 5500, 6500, 5200, 0);
INSERT INTO `yiqing` (`id`, `city_id`, `now_ definite`, `definite`, `death`, `cure`, `yesterday`) VALUES (6, 3, 700, 550, 650, 500, 1);
COMMIT;

-- ----------------------------
-- Procedure structure for zxt
-- ----------------------------
DROP PROCEDURE IF EXISTS `zxt`;
delimiter ;;
CREATE PROCEDURE `zxt`(IN yest int)
BEGIN
SELECT  
c.name as xdata,
y.definite as ydata1,
y.death as ydata2,
y.cure as ydata3

from yiqing  y
LEFT JOIN city  c on y.city_id = c.id
where yesterday = yest;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
